package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.Supplier;

public class DBSupplier implements IFDBSupplier {
	private Connection con;

	public DBSupplier() {
		con = DBConnection.getInstance().getDBcon();
	}

	public Supplier getSupplier(int supplierId, boolean retrieveAssociation) {
		String wClause = " supplierId = " + supplierId;
		return singleWhere(wClause, retrieveAssociation);
	}

	public int insertSupplier(Supplier supplier) throws Exception {
		int nextId = GetMaxId.getMaxId("Select max(supplierId)From Supplier") + 1;
		System.out.println("next supplierId =" + nextId);
		int rc = -1;
		String query = "INSERT INTO supplier(supplierId, name, address, country, phone, email) VALUES ('"
				+ nextId
				+ "','"
				+ supplier.getName()
				+ "','"
				+ supplier.getAddress()
				+ "','"
				+ supplier.getCountry()
				+ "','"
				+ supplier.getPhone() + "','" + supplier.getEmail() + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("Supplier is not inserted");
			throw new Exception("Supplier is not inserted correct");
		}
		return rc;
	}

	public ArrayList<Supplier> getAllSuppliers(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	public int updateSupplier(Supplier supplier) {
		Supplier supplierObj = supplier;
		int rc = -1;
		String query = "UPDATE supplier SET" + "id = '" + supplierObj.getId()
				+ "'," + "name = '" + supplierObj.getName() + "',"
				+ "address = '" + supplierObj.getAddress() + "',"
				+ "country = '" + supplierObj.getCountry() + "'," + "phone = '"
				+ supplierObj.getPhone() + "'," + "email = '"
				+ supplierObj.getEmail() + "', ";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in supplier db: " + e);
		}
		return rc;
	}

	private Supplier singleWhere(String wClause, boolean retrieveAssocciation) {
		ResultSet results;

		Supplier supplierObj = new Supplier();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				supplierObj = biuldSupplier(results);
				stmt.close();
			}
		} catch (Exception e) {
			System.out.println("error in biulding the supplier object");
		}
		return supplierObj;
	}

	private ArrayList<Supplier> miscWhere(String wClause,
			boolean retriveAssociation) {
		ResultSet results;
		ArrayList<Supplier> list = new ArrayList<Supplier>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Supplier supplierObj = new Supplier();
				supplierObj = biuldSupplier(results);
				list.add(supplierObj);
			}
			stmt.close();
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Supplier biuldSupplier(ResultSet results) {
		Supplier supplierObj = new Supplier();
		try {
			supplierObj.setId(results.getInt("supplierId"));
			supplierObj.setName(results.getString("name"));
			supplierObj.setAddress(results.getString("address"));
			supplierObj.setCountry(results.getString("country"));
			supplierObj.setPhone(results.getLong("phone"));
			supplierObj.setEmail(results.getString("email"));
		} catch (Exception e) {
			System.out.println("error in biulding the supplier object");
		}
		return supplierObj;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT supplierId, name, address, country, phone, email FROM supplier";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}
	// td
}